package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblLegalFrozenaccount;

public class TblLegalFrozenaccountMySqlMapperSqlConfig {

    public String findListByPageInfo(PageInfo<TblLegalFrozenaccount> pageInfo, TblLegalFrozenaccount frozenAccount,Integer disputeid) {
        StringBuffer sqlSb = new StringBuffer("SELECT * FROM (SELECT BUDGET.*,ROWNUM RN FROM (select * from TBL_LEGAL_FROZENACCOUNT tlf " +
                "LEFT JOIN TBL_STAFF ts on tlf.CREATESTAFF = ts.STAFFID  " +
                "LEFT JOIN TBL_LEGAL_PROCEEDINGSRECORD tlp on tlf.PROCEEDINFO = tlp.PROCEEDID " +
                "LEFT JOIN TBL_LEGAL_LITIGATIONSETTLEMENT tll ON tlp.LITIGATIONINFO = tll.LITIGATIONID " +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tll.DISPUTEINFO = tld.DISPUTEID " +
                "WHERE tlf.LINKORG = "+ frozenAccount.getLinkorg() );

        if (disputeid != null){
            sqlSb.append(" AND tld.DISPUTEID = " + disputeid);
        }
        if (frozenAccount.getRealname() != null) {
            sqlSb.append(" AND ts.REALNAME like '%" + frozenAccount.getRealname() + "%' ");
        }
        if (frozenAccount.getFrozenblank() != null && !"".equals(frozenAccount.getFrozenblank())) {
            sqlSb.append(" AND tlf.FROZENBLANK LIKE '%" + frozenAccount.getFrozenblank() + "%'");
        }
        if (frozenAccount.getFrozenaccount() != null && !"".equals(frozenAccount.getFrozenaccount())) {
            sqlSb.append(" AND tlf.FROZENACCOUNT LIKE '%" + frozenAccount.getFrozenaccount() + "%'");
        }
        if (frozenAccount.getAccountnature() != null && !"".equals(frozenAccount.getAccountnature())) {
            sqlSb.append(" AND tlf.ACCOUNTNATURE LIKE '%" + frozenAccount.getAccountnature() + "%'");
        }
        if (frozenAccount.getApplyamount() != null) {
            sqlSb.append(" AND tlf.APPLYAMOUNT  >= " + frozenAccount.getApplyamount());
        }
        if (frozenAccount.getMaxApplyAmount() != null) {
            sqlSb.append(" AND tlf.APPLYAMOUNT  <= " + frozenAccount.getMaxApplyAmount());
        }
        if (frozenAccount.getFrozenamount() != null) {
            sqlSb.append(" AND tlf.FROZENAMOUNT  <= " + frozenAccount.getFrozenamount());
        }
        if (frozenAccount.getMinFrozenAmount() != null) {
            sqlSb.append(" AND tlf.FROZENAMOUNT  >= " + frozenAccount.getMinFrozenAmount());
        }
        if (frozenAccount.getKouhuaamount() != null) {
            sqlSb.append(" AND tlf.KOUHUAAMOUNT  <= " + frozenAccount.getKouhuaamount());
        }
        if (frozenAccount.getMinKouhuaAmount() != null) {
            sqlSb.append(" AND tlf.KOUHUAAMOUNT  >= " + frozenAccount.getMinKouhuaAmount());
        }

        sqlSb.append(" ORDER BY inforId DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RN > "+pageInfo.getCurrentRecord());
        String sql = sqlSb.toString();
        return sql;
    }


    public String findListByPageInfoCount(TblLegalFrozenaccount frozenAccount,Integer disputeid) {
        StringBuffer sqlSb = new StringBuffer("select COUNT(*) from TBL_LEGAL_FROZENACCOUNT tlf " +
                "LEFT JOIN TBL_STAFF ts on tlf.CREATESTAFF = ts.STAFFID  " +
                "LEFT JOIN TBL_LEGAL_PROCEEDINGSRECORD tlp on tlf.PROCEEDINFO = tlp.PROCEEDID " +
                "LEFT JOIN TBL_LEGAL_LITIGATIONSETTLEMENT tll ON tlp.LITIGATIONINFO = tll.LITIGATIONID " +
                "LEFT JOIN TBL_LEGAL_DISPUTREGISTRATION tld on tll.DISPUTEINFO = tld.DISPUTEID " +
                "WHERE tlf.LINKORG = "+ frozenAccount.getLinkorg() );

        if (disputeid != null){
            sqlSb.append(" AND tld.DISPUTEID = " + disputeid);
        }
        if (frozenAccount.getRealname() != null) {
            sqlSb.append(" AND ts.REALNAME like '%" + frozenAccount.getRealname() + "%' ");
        }
        if (frozenAccount.getFrozenblank() != null && !"".equals(frozenAccount.getFrozenblank())) {
            sqlSb.append(" AND tlf.FROZENBLANK LIKE '%" + frozenAccount.getFrozenblank() + "%'");
        }
        if (frozenAccount.getFrozenaccount() != null && !"".equals(frozenAccount.getFrozenaccount())) {
            sqlSb.append(" AND tlf.FROZENACCOUNT LIKE '%" + frozenAccount.getFrozenaccount() + "%'");
        }
        if (frozenAccount.getAccountnature() != null && !"".equals(frozenAccount.getAccountnature())) {
            sqlSb.append(" AND tlf.ACCOUNTNATURE LIKE '%" + frozenAccount.getAccountnature() + "%'");
        }
        if (frozenAccount.getApplyamount() != null) {
            sqlSb.append(" AND tlf.APPLYAMOUNT  >= " + frozenAccount.getApplyamount());
        }
        if (frozenAccount.getMaxApplyAmount() != null) {
            sqlSb.append(" AND tlf.APPLYAMOUNT  <= " + frozenAccount.getMaxApplyAmount());
        }
        if (frozenAccount.getFrozenamount() != null) {
            sqlSb.append(" AND tlf.FROZENAMOUNT  <= " + frozenAccount.getFrozenamount());
        }
        if (frozenAccount.getMinFrozenAmount() != null) {
            sqlSb.append(" AND tlf.FROZENAMOUNT  >= " + frozenAccount.getMinFrozenAmount());
        }
        if (frozenAccount.getKouhuaamount() != null) {
            sqlSb.append(" AND tlf.KOUHUAAMOUNT  <= " + frozenAccount.getKouhuaamount());
        }
        if (frozenAccount.getMinKouhuaAmount() != null) {
            sqlSb.append(" AND tlf.KOUHUAAMOUNT  >= " + frozenAccount.getMinKouhuaAmount());
        }

        sqlSb.append(" ORDER BY inforId DESC ");
        String sql = sqlSb.toString();
        return sql;
    }


    public String saveFrozenAccount(TblLegalFrozenaccount frozen) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_LEGAL_FROZENACCOUNT (INFORID");
        StringBuffer value = new StringBuffer(" VALUES (CIRCULATION_SEQUENCE.nextval");

        if(frozen.getProceedinfo() != null && !"".equals(frozen.getProceedinfo())) {
            column.append(",PROCEEDINFO");
            value.append(",'"+frozen.getProceedinfo()+"'");
        }
        if(frozen.getCreatetime() != null && !"".equals(frozen.getCreatetime())) {
            column.append(",CREATETIME");
            value.append(",'"+frozen.getCreatetime()+"'");
        }
        if(frozen.getCreatestaff() != null && !"".equals(frozen.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'"+frozen.getCreatestaff()+"'");
        }
        if(frozen.getLinkorg() != null && !"".equals(frozen.getLinkorg())) {
            column.append(",LINKORG");
            value.append(",'"+frozen.getLinkorg()+"'");
        }
        if(frozen.getFrozenblank() != null && !"".equals(frozen.getFrozenblank())) {
            column.append(",FROZENBLANK");
            value.append(",'"+frozen.getFrozenblank()+"'");
        }
        if(frozen.getFrozenaccount() != null && !"".equals(frozen.getFrozenaccount())) {
            column.append(",FROZENACCOUNT");
            value.append(",'"+frozen.getFrozenaccount()+"'");
        }
        if(frozen.getApplyamount() != null && !"".equals(frozen.getApplyamount())) {
            column.append(",APPLYAMOUNT");
            value.append(",'"+frozen.getApplyamount()+"'");
        }
        if(frozen.getFrozenamount() != null && !"".equals(frozen.getFrozenamount())) {
            column.append(",FROZENAMOUNT");
            value.append(",'"+frozen.getFrozenamount()+"'");
        }
        if(frozen.getKouhuaamount() != null && !"".equals(frozen.getKouhuaamount())) {
            column.append(",KOUHUAAMOUNT");
            value.append(",'"+frozen.getKouhuaamount()+"'");
        }
        if(frozen.getAccountnature() != null && !"".equals(frozen.getAccountnature())) {
            column.append(",ACCOUNTNATURE");
            value.append(",'"+frozen.getAccountnature()+"'");
        }
        if(frozen.getStartdate() != null && !"".equals(frozen.getStartdate())) {
            column.append(",STARTDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(frozen.getStartdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(frozen.getEnddate() != null && !"".equals(frozen.getEnddate())) {
            column.append(",ENDDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(frozen.getEnddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(frozen.getFrozenmemo() != null && !"".equals(frozen.getFrozenmemo())) {
            column.append(",FROZENMEMO");
            value.append(",'"+frozen.getFrozenmemo()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }


    public String updateModifyFrozenAccount(TblLegalFrozenaccount oldFrozen) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_LEGAL_FROZENACCOUNT SET FROZENBLANK = '"+oldFrozen.getFrozenblank()+"'");

        if(oldFrozen.getProceedinfo() != null && !"".equals(oldFrozen.getProceedinfo())) {
            sql.append(" , PROCEEDINFO = '"+oldFrozen.getProceedinfo()+"'");
        }
        if(oldFrozen.getCreatetime() != null && !"".equals(oldFrozen.getCreatetime())) {
            sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(oldFrozen.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldFrozen.getCreatestaff() != null && !"".equals(oldFrozen.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '"+oldFrozen.getCreatestaff()+"'");
        }
        if(oldFrozen.getLinkorg() != null && !"".equals(oldFrozen.getLinkorg())) {
            sql.append(" , LINKORG = '"+oldFrozen.getLinkorg()+"'");
        }
//        if(oldFrozen.getFrozenblank() != null && !"".equals(oldFrozen.getFrozenblank())) {
//            sql.append(" , FROZENBLANK = '"+oldFrozen.getFrozenblank()+"'");
//        }
        if(oldFrozen.getFrozenaccount() != null && !"".equals(oldFrozen.getFrozenaccount())) {
            sql.append(" , FROZENACCOUNT = '"+oldFrozen.getFrozenaccount()+"'");
        }
        if(oldFrozen.getApplyamount() != null && !"".equals(oldFrozen.getApplyamount())) {
            sql.append(" , APPLYAMOUNT = '"+oldFrozen.getApplyamount()+"'");
        }
        if(oldFrozen.getFrozenamount() != null && !"".equals(oldFrozen.getFrozenamount())) {
            sql.append(" , FROZENAMOUNT = '"+oldFrozen.getFrozenamount()+"'");
        }
        if(oldFrozen.getKouhuaamount() != null && !"".equals(oldFrozen.getKouhuaamount())) {
            sql.append(" , KOUHUAAMOUNT = '"+oldFrozen.getKouhuaamount()+"'");
        }
        if(oldFrozen.getAccountnature() != null && !"".equals(oldFrozen.getAccountnature())) {
            sql.append(" , ACCOUNTNATURE = '"+oldFrozen.getAccountnature()+"'");
        }
        if(oldFrozen.getStartdate() != null && !"".equals(oldFrozen.getStartdate())) {
            sql.append(" ,STARTDATE = TO_DATE('"+DateUtil.parseDate(oldFrozen.getStartdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldFrozen.getEnddate() != null && !"".equals(oldFrozen.getEnddate())) {
            sql.append(" ,ENDDATE = TO_DATE('"+DateUtil.parseDate(oldFrozen.getEnddate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldFrozen.getFrozenmemo() != null && !"".equals(oldFrozen.getFrozenmemo())) {
            sql.append(" , FROZENMEMO = '"+oldFrozen.getFrozenmemo()+"'");
        }

        sql.append(" WHERE INFORID = '"+oldFrozen.getInforid()+"'");
        return sql.toString();
    }
}
